﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;

namespace PTTPOS_Solution.DAL
{
    public static class Order_pro_DA
    {
        public static DataTable ListOrder = new DataTable();
        public static DataTable ListFindOrder = new DataTable();
        public static void CheckDiscount_pro(string id)
        {
            MySqlCommand com = new MySqlCommand("select id_discount, Rebateform, value from tblorders_pro inner join tbldiscounts on tbldiscounts.id = tblorders.id_discount where tblorders_pro.id=@id and id_discount <>''", Connection_PTTPOS.GetConnection());

            try
            {
                com.Connection.Open();
                com.Parameters.AddWithValue("@id", id);
                MySqlDataAdapter da = new MySqlDataAdapter(com);
                ListOrder.Clear();
                da.Fill(ListOrder);

            }
            finally
            {
                com.Connection.Close();
            }
        }
        public static bool TotalOrder_pro()
        {
            bool chk = false;
            MySqlCommand com = new MySqlCommand("select count(*) from tblorders_pro", Connection_PTTPOS.GetConnection());
            com.Connection.Open();
            MySqlDataReader reader = com.ExecuteReader();
            if (reader.Read())
            {
                if (Convert.ToInt64(reader[0]) >= 200)
                {
                    chk = true;
                }
            }
            com.Connection.Close();
            return chk;
        }
        public static void FindOrder_pro(string ID_table)
        {
            MySqlCommand com = new MySqlCommand("select * from tblorders_pro where id_table=@id_table", Connection_PTTPOS.GetConnection());

            try
            {
                com.Connection.Open();
                com.Parameters.AddWithValue("@ID_table", ID_table);
                ListFindOrder.Clear();
                MySqlDataAdapter da = new MySqlDataAdapter(com);
                da.Fill(ListFindOrder);

            }
            finally
            {
                com.Connection.Close();
            }
        }

        public static string GetMaxOrder_pro()
        {
            MySqlCommand com = new MySqlCommand("select max(id) as id from tblorders_pro", Connection_PTTPOS.GetConnection());
            try
            {
                com.Connection.Open();
                MySqlDataReader dr = com.ExecuteReader();
                if (dr.Read())
                    return dr["id"].ToString();
                else
                    return "";
            }
            finally
            {
                com.Connection.Close();
            }
        }

        public static string FindSurcharge_pro(string id)
        {
            MySqlCommand com = new MySqlCommand("select Value from tblorders_pro inner join tblsurcharges on tblsurcharges.id=tblorders_pro.id_surcharge where tblorders_pro.id=@id and id_surcharge <> ''", Connection_PTTPOS.GetConnection());
            try
            {
                com.Connection.Open();
                com.Parameters.AddWithValue("@id", id);
                MySqlDataReader dr = com.ExecuteReader();
                if (dr.Read())
                    return dr["Value"].ToString();
                else
                    return "";
            }
            finally
            {
                com.Connection.Close();
            }
        }

        public static void InsertOrder_pro(string id_customer, string id_staff, string id_table, string id_surcharge, string id_discount, DateTime Orderdate, decimal total_surcharge, decimal total_discount, decimal total_price)
        {
            MySqlCommand com = new MySqlCommand("insert into tblorders_pro(id,id_customer,id_staff, id_table, id_surcharge, id_discount, Orderdate, total_surcharge, total_discount, total_price) values(null, @id_customer, @id_staff, @id_table, @id_surcharge, @id_discount, @Orderdate, @total_surcharge, @total_discount, @total_price)", Connection_PTTPOS.GetConnection());
            com.Parameters.AddWithValue("@id_customer", id_customer);
            com.Parameters.AddWithValue("@id_staff", id_staff);
            com.Parameters.AddWithValue("@id_table", id_table);
            com.Parameters.AddWithValue("@id_surcharge", id_surcharge);
            com.Parameters.AddWithValue("@id_discount", id_discount);
            com.Parameters.AddWithValue("@Orderdate", Orderdate);
            com.Parameters.AddWithValue("@total_surcharge", total_surcharge);
            com.Parameters.AddWithValue("@total_discount", total_discount);
            com.Parameters.AddWithValue("@total_price", total_price);
            com.Connection.Open();
            com.ExecuteNonQuery();
            com.Connection.Close();
        }

        public static void UpdateOrder_pro_surcharge2(string id, decimal total_surcharge, decimal total_price)
        {
            MySqlCommand com = new MySqlCommand("update tblorders_pro set total_surcharge=@total_surcharge, total_price=@total_price where id=@id", Connection_PTTPOS.GetConnection());
            com.Parameters.AddWithValue("@id", id);
            com.Parameters.AddWithValue("@total_surcharge", total_surcharge);
            com.Parameters.AddWithValue("@total_price", total_price);
            com.Connection.Open();
            com.ExecuteNonQuery();
            com.Connection.Close();
        }

        public static void UpdateOrder_pro_surcharge(string id, string id_surcharge, decimal total_surcharge, decimal total_price)
        {
            MySqlCommand com = new MySqlCommand("update tblorders_pro set id_surcharge = @id_surcharge, total_surcharge=@total_surcharge, total_price=@total_price where id=@id", Connection_PTTPOS.GetConnection());
            com.Parameters.AddWithValue("@id", id);
            com.Parameters.AddWithValue("@id_surcharge", id_surcharge);
            com.Parameters.AddWithValue("@total_surcharge", total_surcharge);
            com.Parameters.AddWithValue("@total_price", total_price);
            com.Connection.Open();
            com.ExecuteNonQuery();
            com.Connection.Close();
        }

        public static void UpdateOrder_pro_Customer(string id, string id_customer, decimal total_discount, decimal total_price)
        {
            MySqlCommand com = new MySqlCommand("update tblorders_pro set id_customer = @id_customer, total_discount=@total_discount, total_price=@total_price where id=@id", Connection_PTTPOS.GetConnection());
            com.Parameters.AddWithValue("@id", id);
            com.Parameters.AddWithValue("@id_customer", id_customer);
            com.Parameters.AddWithValue("@total_discount", total_discount);
            com.Parameters.AddWithValue("@total_price", total_price);
            com.Connection.Open();
            com.ExecuteNonQuery();
            com.Connection.Close();
        }

        public static void UpdateOrder_pro_DiscountDetail(string id, decimal total_discount, decimal total_price)
        {
            MySqlCommand com = new MySqlCommand("update tblorders_pro set total_discount=@total_discount, total_price=@total_price where id=@id", Connection_PTTPOS.GetConnection());
            com.Parameters.AddWithValue("@id", id);
            com.Parameters.AddWithValue("@total_discount", total_discount);
            com.Parameters.AddWithValue("@total_price", total_price);
            com.Connection.Open();
            com.ExecuteNonQuery();
            com.Connection.Close();
        }

        public static void UpdateOrder_pro_Discount(string id, string id_discount, decimal total_discount, decimal total_price)
        {
            MySqlCommand com = new MySqlCommand("update tblorders_pro set id_discount=@id_discount, total_discount=@total_discount, total_price=@total_price where id=@id", Connection_PTTPOS.GetConnection());
            com.Parameters.AddWithValue("@id", id);
            com.Parameters.AddWithValue("@id_discount", id_discount);
            com.Parameters.AddWithValue("@total_discount", total_discount);
            com.Parameters.AddWithValue("@total_price", total_price);
            com.Connection.Open();
            com.ExecuteNonQuery();
            com.Connection.Close();
        }

        public static void UpdateOrder_pro(string id, string id_customer, string id_staff, string id_table, string id_surcharge, string id_discount, decimal total_surcharge, decimal total_discount, decimal total_price)
        {
            MySqlCommand com = new MySqlCommand("update tblorders_pro set id_customer =@id_customer,id_staff=@id_staff, id_table=@id_table, id_surcharge=@id_surcharge, id_discount=@id_discount, total_surcharge=@total_surcharge, total_discount=@total_discount, total_price=@total_price where id=@id", Connection_PTTPOS.GetConnection());
            com.Parameters.AddWithValue("@id", id);
            com.Parameters.AddWithValue("@id_customer", id_customer);
            com.Parameters.AddWithValue("@id_staff", id_staff);
            com.Parameters.AddWithValue("@id_table", id_table);
            com.Parameters.AddWithValue("@id_surcharge", id_surcharge);
            com.Parameters.AddWithValue("@id_discount", id_discount);
            //com.Parameters.AddWithValue("@Orderdate", Orderdate);
            com.Parameters.AddWithValue("@total_surcharge", total_surcharge);
            com.Parameters.AddWithValue("@total_discount", total_discount);
            com.Parameters.AddWithValue("@total_price", total_price);
            com.Connection.Open();
            com.ExecuteNonQuery();
            com.Connection.Close();
        }

        public static DataTable GetlistOrder2_pro(string id_order) // Lấy dữ liệu làm nguồn cho report Hóa đơn tính tiền ( Gộp 2 bảng Orderdetail + Orderdetail 2)
        {
            DataTable tbl = new DataTable();
            MySqlCommand com = new MySqlCommand(@"select tblorders_pro.id ID, orderdate, nametable, tblstaffs.fullname Fullname, productname Name, Amount, tblorderdetail2s_pro.Price, total_surcharge, total_discount, total_price, tblcustomers.fullname Fullname1 from 
((((tblorders_pro inner join tblstaffs on tblstaffs.id=tblorders_pro.id_staff) 
left join tbltables on tbltables.id=tblorders_pro.id_table) 
left join tblcustomers on tblcustomers.id=tblorders_pro.id_customer)
inner join tblorderdetail2s_pro on tblorderdetail2s_pro.id_order=tblorders_pro.id) 

where tblorders_pro.id=@id and productname<>''", Connection_PTTPOS.GetConnection());
            com.Parameters.AddWithValue("@id", id_order);
            com.Connection.Open();
            MySqlDataAdapter adapter = new MySqlDataAdapter(com);
            adapter.Fill(tbl);
            com.Connection.Close();
            return tbl;
        }
        /// <summary>
        /// Dùng cho nút xác nhận của tách bàn
        /// </summary>
        public static DataTable GetlistOrder2_pro(string id_order, List<string> lstproductname) // Lấy dữ liệu làm nguồn cho report Hóa đơn tính tiền ( Gộp 2 bảng Orderdetail + Orderdetail 2)
        {
            DataTable tbl = new DataTable();
            foreach (string strProductname in lstproductname)
            {
                MySqlCommand com = new MySqlCommand(@"select tblorders_pro.id ID, orderdate, nametable, tblstaffs.fullname Fullname, productname Name, @amount Amount, tblorderdetail2s_pro.Price, @total_surcharge total_surcharge, @total_discount total_discount,@total_price total_price, tblcustomers.fullname Fullname1 from 
((((tblorders_pro inner join tblstaffs on tblstaffs.id=tblorders_pro.id_staff) 
left join tbltables on tbltables.id=tblorders_pro.id_table) 
left join tblcustomers on tblcustomers.id=tblorders_pro.id_customer)
inner join tblorderdetail2s_pro on tblorderdetail2s_pro.id_order=tblorders_pro.id) 

where tblorders_pro.id=@id and productname=@prodctname", Connection_PTTPOS.GetConnection());
                com.Parameters.AddWithValue("@prodctname", strProductname.Split('|')[0]);
                com.Parameters.AddWithValue("@amount", strProductname.Split('|')[1]);
                com.Parameters.AddWithValue("@total_surcharge", strProductname.Split('|')[2]);
                com.Parameters.AddWithValue("@total_discount", strProductname.Split('|')[3]);
                com.Parameters.AddWithValue("@total_price", strProductname.Split('|')[4]);
                com.Parameters.AddWithValue("@id", id_order);
                com.Connection.Open();
                MySqlDataAdapter adapter = new MySqlDataAdapter(com);
                adapter.Fill(tbl);
                com.Connection.Close();
            }
            return tbl;
        }
        public static DataTable GetlistOrder_pro(string id_order) // Lấy dữ liệu làm nguồn cho report Hóa đơn tính tiền ( Gộp 2 bảng Orderdetail + Orderdetail 2)
        {
            DataTable tbl = new DataTable();
            
            MySqlCommand com = new MySqlCommand(@"select tblorders_pro.id ID, orderdate, nametable, tblstaffs.fullname Fullname, Name, Amount, tblorderdetails_pro.Price, total_surcharge, total_discount, total_price, tblcustomers.fullname Fullname1 from 
(((((tblorders_pro inner join tblstaffs on tblstaffs.id=tblorders_pro.id_staff) 
left join tbltables on tbltables.id=tblorders_pro.id_table) 
left join tblcustomers on tblcustomers.id=tblorders_pro.id_customer) 
inner join tblorderdetails_pro on tblorderdetails_pro.id_order=tblorders_pro.id) 
inner join tblmenus on tblmenus.id=tblorderdetails_pro.id_menu) 

where tblorders_pro.id=@id", Connection_PTTPOS.GetConnection());
            com.Parameters.AddWithValue("@id", id_order);
            com.Connection.Open();
            MySqlDataAdapter adapter = new MySqlDataAdapter(com);
            adapter.Fill(tbl);
            com.Connection.Close();
            return tbl;
        }
        /// <summary>
        /// Dùng cho nút xác nhận của tách bàn
        /// </summary>
        public static DataTable GetlistOrder_pro(string id_order, List<string> lstid_menu) // Lấy dữ liệu làm nguồn cho report Hóa đơn tính tiền ( Gộp 2 bảng Orderdetail + Orderdetail 2)
        {
            DataTable tbl = new DataTable();
            foreach (string strIdMenu in lstid_menu)
            {
                MySqlCommand com = new MySqlCommand(@"select tblorders_pro.id ID, orderdate, nametable, tblstaffs.fullname Fullname, Name, @amount Amount, tblorderdetails_pro.Price, @total_surcharge total_surcharge, @total_discount total_discount, @total_price total_price, tblcustomers.fullname Fullname1 from 
(((((tblorders_pro inner join tblstaffs on tblstaffs.id=tblorders_pro.id_staff) 
left join tbltables on tbltables.id=tblorders_pro.id_table) 
left join tblcustomers on tblcustomers.id=tblorders_pro.id_customer) 
inner join tblorderdetails_pro on tblorderdetails_pro.id_order=tblorders_pro.id) 
inner join tblmenus on tblmenus.id=tblorderdetails_pro.id_menu) 

where tblorders_pro.id=@id and tblmenus.id=@id_menu", Connection_PTTPOS.GetConnection());
                com.Parameters.AddWithValue("@id", id_order);
                com.Parameters.AddWithValue("@id_menu", strIdMenu.Split('|')[0]);
                com.Parameters.AddWithValue("@amount",strIdMenu.Split('|')[1]);
                com.Parameters.AddWithValue("@total_surcharge", strIdMenu.Split('|')[2]);
                com.Parameters.AddWithValue("@total_discount", strIdMenu.Split('|')[3]);
                com.Parameters.AddWithValue("@total_price", strIdMenu.Split('|')[4]);
                com.Connection.Open();
                MySqlDataAdapter adapter = new MySqlDataAdapter(com);
                adapter.Fill(tbl);
                com.Connection.Close();
            }
            return tbl;
        }

        public static DataTable getlistOrder_pro(string id_order) // Phương thức lấy danh dách Order trong ngày của nhân viên ( Gộp 2 bảng getListReportShift 1 + getListReportShift 2)
        {
            DataTable tbl = new DataTable();
            tbl.Columns.Add("ID");
            tbl.Columns.Add("Orderdate");
            tbl.Columns.Add("Nametable");
            tbl.Columns.Add("Fullname");
            tbl.Columns.Add("Name");
            tbl.Columns.Add("Amount");
            tbl.Columns.Add("Price");
            tbl.Columns.Add("total_surcharge");
            tbl.Columns.Add("total_discount");
            tbl.Columns.Add("total_price");
            tbl.Columns.Add("VAT");
            tbl.Columns.Add("Fullname1");

            foreach (DataRow row in GetlistOrder_pro(id_order).Rows)
            {
                tbl.Rows.Add(row["ID"].ToString(), row["orderdate"].ToString(), row["Nametable"].ToString(), row["Fullname"].ToString(), row["Name"].ToString(), row["Amount"].ToString(), row["Price"].ToString(), row["total_surcharge"].ToString(), row["total_discount"].ToString(), row["total_price"].ToString(), "10%", row["Fullname1"].ToString());
            }
            foreach (DataRow row in GetlistOrder2_pro(id_order).Rows)
            {
                tbl.Rows.Add(row["ID"].ToString(), row["orderdate"].ToString(), row["Nametable"].ToString(), row["Fullname"].ToString(), row["Name"].ToString(), row["Amount"].ToString(), row["Price"].ToString(), row["total_surcharge"].ToString(), row["total_discount"].ToString(), row["total_price"].ToString(), "10%", row["Fullname1"].ToString());
            }
            return tbl;
        }
        public static DataTable getListReportShift1_pro(string id_staff)
        {
            DataTable tbl = new DataTable();
            MySqlCommand com = new MySqlCommand(@"
select count(*) stt, tblstaffs.fullname, Orderdate, tblorders_pro.id ID, Nametable, tblcustomers.fullname Fullname1, sum(total_price) total_price ,(select sum(total_price) from tblorders_pro) total_priceReal 
from ((tbltables right join tblorders_pro on tblorders_pro.id_table = tbltables.id)
inner join tblstaffs on tblstaffs.id=tblorders_pro.id_staff)
left join tblcustomers on tblcustomers.id=tblorders_pro.id_customer
where tblstaffs.id=@id and date(orderdate)=@date
group by tblstaffs.fullname, Orderdate, tblorders_pro.id, Nametable, tblcustomers.fullname", Connection_PTTPOS.GetConnection());
            com.Parameters.AddWithValue("@id", id_staff);
            com.Parameters.AddWithValue("@date", DateTime.Today.Date);
            com.Connection.Open();
            MySqlDataAdapter adapter = new MySqlDataAdapter(com);
            adapter.Fill(tbl);
            com.Connection.Close();
            return tbl;
        }
        public static void DeleteOrder_pro(string id_order) // Phương thức Hủy hóa đơn
        {
            MySqlCommand command = new MySqlCommand("delete from tblorders_pro where id=@id_order", Connection_PTTPOS.GetConnection());
            command.Parameters.AddWithValue("@id_order", id_order);
            command.Connection.Open();
            command.ExecuteNonQuery();
            command.Connection.Close();
        }

        //public static void UpdateOrders_pro_Pay(string id_order)
        //{
        //    MySqlCommand command = new MySqlCommand("update tblorders_pro set status=1 where id=@id", Connection_PTTPOS.GetConnection());
        //    command.Parameters.AddWithValue("@id", id_order);
        //    command.Connection.Open();
        //    command.ExecuteNonQuery();
        //    command.Connection.Close();
        //}
        public static void Update_Pro_BreakTable(string id_order) // Hàm này cho bên form tách bàn
        {
            MySqlCommand command2 = new MySqlCommand("update tblOrders_Pro set total_discount=null, id_surcharge=null,id_customer=null where id=@id_order", Connection_PTTPOS.GetConnection());
                command2.Connection.Open();
                command2.Parameters.AddWithValue("@id_order", id_order);
                command2.ExecuteNonQuery();
                command2.Connection.Close();
        }
        public static void Update_Pro_BreakTable(string id_order, decimal totalDiscount, decimal totalSurcharge, decimal totalPrice) // Hàm này cho bên form main
        {
            MySqlCommand command2 = new MySqlCommand("update tblOrders_Pro set total_surcharge=@total_surcharge, total_price=@total_price, total_discount=@toal_discount where id=@id_order", Connection_PTTPOS.GetConnection());
            command2.Connection.Open();
            command2.Parameters.AddWithValue("@id_order", id_order);
            command2.Parameters.AddWithValue("@total_surcharge", totalSurcharge);
            command2.Parameters.AddWithValue("@total_price", totalPrice);
            command2.Parameters.AddWithValue("@toal_discount", totalDiscount);
            command2.ExecuteNonQuery();
            command2.Connection.Close();
        }

        public static void UpdateOrders_pro_Compile(string id_order, string id_discount, string id_customer) // Phương thức giành cho gộp bàn
        {
            MySqlCommand command = new MySqlCommand(@"
update tblorders_pro 
set id_discount=@id_discount, 
id_customer=@id_customer
where id=@id", Connection_PTTPOS.GetConnection());
            command.Parameters.AddWithValue("@id", id_order);
            command.Parameters.AddWithValue("@id_discount", id_discount);
            command.Parameters.AddWithValue("@id_customer", id_customer);
            command.Connection.Open();
            command.ExecuteNonQuery();
            command.Connection.Close();
        }
        public static void UpdateOrdersSurcharge_pro_Compile(string id_order, string id_surcharge, decimal total_surcharge, decimal total_price) // Phương thức giành cho gộp bàn
        {
            MySqlCommand command = new MySqlCommand(@"
update tblorders_pro 
set id_surcharge=@id_surcharge, total_surcharge=@total_surcharge, total_price=@total_price 
where id=@id", Connection_PTTPOS.GetConnection());
            command.Parameters.AddWithValue("@id", id_order);
            command.Parameters.AddWithValue("@total_surcharge", total_surcharge);
            command.Parameters.AddWithValue("@total_price", total_price);
            command.Parameters.AddWithValue("@id_surcharge", id_surcharge);

            command.Connection.Open();
            command.ExecuteNonQuery();
            command.Connection.Close();
        }
        public static void UpdateOrdersDiscount_pro_Compile(string id_order, decimal discount, decimal totalprice) // Phương thức giành cho gộp bàn
        {
            MySqlCommand command = new MySqlCommand(@"
update tblorders_pro 
set total_discount=@total_discount , total_price=@total_price 
where id=@id", Connection_PTTPOS.GetConnection());
            command.Parameters.AddWithValue("@id", id_order);
            command.Parameters.AddWithValue("@total_discount", discount);
            command.Parameters.AddWithValue("@total_price", totalprice);
            command.Connection.Open();
            command.ExecuteNonQuery();
            command.Connection.Close();
        }
        public static void Swaptable_pro(string newtbl, string id)
        {
            MySqlCommand command = new MySqlCommand("update tblorders_pro set id_table=@newtbl where id=@id", Connection_PTTPOS.GetConnection());
            command.Parameters.AddWithValue("@newtbl", newtbl);
            command.Parameters.AddWithValue("@id", id);
            command.Connection.Open();
            command.ExecuteNonQuery();
            command.Connection.Close();
        }
        public static void UpdateOrder_pro_totalprice(decimal price, string id_order, decimal total_discount, decimal total_surcharge)
        {
            MySqlCommand command = new MySqlCommand("update tblorders_pro set total_price=@price, total_discount=@total_discount, total_surcharge=@total_surchage where id=@id", Connection_PTTPOS.GetConnection());
            command.Parameters.AddWithValue("@price", price);
            command.Parameters.AddWithValue("@id", id_order);
            command.Parameters.AddWithValue("@total_discount", total_discount);
            command.Parameters.AddWithValue("@total_surchage", total_surcharge);
            command.Connection.Open();
            command.ExecuteNonQuery();
            command.Connection.Close();
        }
    }
}
